package com.it2297.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.it2297.main.App;

public class UsedItem
{
	private int id;
	private String uuid;
	private String title;
	private String description;
	private String type;
	private int stockCount;
	private Timestamp dateCreate;
	private Timestamp dateUpdate;
	private ItemCondition itemCondition;
	private Status status;
	private String comment;
	private String donorNRIC;
	private String wonBy;

	public static ArrayList<UsedItem> getMultiple(int index, int count, Status applicationStatus, SortBy sortType, Type filterType, String titleKeywords, String descriptionKeywords)
	{
		ArrayList<UsedItem> usedItems = new ArrayList<UsedItem>();
		String query;

		// criterias
		String applicationStatusKey;
		String sortTypeKey;
		String filterTypeKey;
		String delimitedTitleKeywords;
		String delimitedDescriptionKeywords;

		if (titleKeywords == null)
		{
			delimitedTitleKeywords = ".*";
		}
		else if (titleKeywords.trim().replaceAll(" +", "|").isEmpty())
		{
			delimitedTitleKeywords = ".*";
		}
		else
		{
			delimitedTitleKeywords = titleKeywords.trim().replaceAll(" +", "|");
		}

		if (descriptionKeywords == null)
		{
			delimitedDescriptionKeywords = ".*";
		}
		else if (descriptionKeywords.trim().replaceAll(" +", "|").isEmpty())
		{
			delimitedDescriptionKeywords = ".*";
		}
		else
		{
			delimitedDescriptionKeywords = descriptionKeywords.trim().replaceAll(" +", "|");
		}

		if (applicationStatus == null)
		{
			applicationStatusKey = ".*";
		}
		else
		{
			applicationStatusKey = applicationStatus.name();
		}

		if (filterType == null)
		{
			filterTypeKey = ".*";
		}
		else
		{
			filterTypeKey = filterType.name();
		}

		switch (sortType)
		{
			case TITLE:
				sortTypeKey = "title";
				break;
			case TYPE:
				sortTypeKey = "type";
				break;
			case MOST_RECENT:
				sortTypeKey = "date_create desc";
				break;
			case ITEM_CONDITION:
				sortTypeKey = "item_condition";
				break;
			default:
				sortTypeKey = "title";
				break;
		}

		query = "SELECT * FROM americano.used_item WHERE status REGEXP(?) AND type REGEXP(?) AND title REGEXP(?) AND description REGEXP(?) ORDER BY " + sortTypeKey + " LIMIT ?, ?";

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(query);
			preparedStatement.setString(1, applicationStatusKey);
			preparedStatement.setString(2, filterTypeKey);
			preparedStatement.setString(3, delimitedTitleKeywords);
			preparedStatement.setString(4, delimitedDescriptionKeywords);
			preparedStatement.setInt(5, index);
			preparedStatement.setInt(6, count);
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				UsedItem usedItem = new UsedItem();
				usedItem.setAttributes(resultSet);
				usedItems.add(usedItem);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return usedItems;
	}

	public static ArrayList<UsedItem> getMultiple(String nric, int month)
	{
		ArrayList<UsedItem> usedItems = new ArrayList<UsedItem>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.used_item WHERE donor_nric = ? AND MONTH( date_create ) = ?");
			preparedStatement.setString(1, nric);
			preparedStatement.setInt(2, month);
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				UsedItem usedItem = new UsedItem();
				usedItem.setAttributes(resultSet);
				usedItems.add(usedItem);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return usedItems;
	}

	public static int getTotalUniqueDonor()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(DISTINCT donor_nric) FROM americano.used_item");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalApproved()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item WHERE status='APPROVED'");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalPending()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item WHERE status='PENDING'");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalRejected()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item WHERE status='REJECTED'");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalEnded()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item WHERE status='ENDED'");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalItemType(Type type)
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item WHERE type=?");
			preparedStatement.setString(1, type.name());
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static Object[][] getTopDonor()
	{
		Object[][] data = new Object[3][3];

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT *, COUNT(DISTINCT donor_nric, id) AS num_of_donations FROM americano.used_item ui INNER JOIN americano.user u ON u.nric = ui.donor_nric ORDER BY num_of_donations DESC LIMIT 0,3");
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				data[resultSet.getRow() - 1][0] = resultSet.getString("nric");
				data[resultSet.getRow() - 1][1] = resultSet.getString("name");
				data[resultSet.getRow() - 1][2] = resultSet.getInt("num_of_donations") + " donations";
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return data;
	}

	public static int getTotalItemDonated()
	{
		int totalItemDonated = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				totalItemDonated = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return totalItemDonated;
	}

	public static int getRecordNum(Status applicationStatus, SortBy sortType, Type filterType, String titleKeywords, String descriptionKeywords)
	{
		int recordNum = 0;
		String query;

		// criterias
		String applicationStatusKey;
		String sortTypeKey;
		String filterTypeKey;
		String delimitedTitleKeywords;
		String delimitedDescriptionKeywords;

		if (titleKeywords == null)
		{
			delimitedTitleKeywords = ".*";
		}
		else if (titleKeywords.trim().replaceAll(" +", "|").isEmpty())
		{
			delimitedTitleKeywords = ".*";
		}
		else
		{
			delimitedTitleKeywords = titleKeywords.trim().replaceAll(" +", "|");
		}

		if (descriptionKeywords == null)
		{
			delimitedDescriptionKeywords = ".*";
		}
		else if (descriptionKeywords.trim().replaceAll(" +", "|").isEmpty())
		{
			delimitedDescriptionKeywords = ".*";
		}
		else
		{
			delimitedDescriptionKeywords = descriptionKeywords.trim().replaceAll(" +", "|");
		}

		if (applicationStatus == null)
		{
			applicationStatusKey = ".*";
		}
		else
		{
			applicationStatusKey = applicationStatus.name();
		}

		if (filterType == null)
		{
			filterTypeKey = ".*";
		}
		else
		{
			filterTypeKey = filterType.name();
		}

		switch (sortType)
		{
			case TITLE:
				sortTypeKey = "title";
				break;
			case TYPE:
				sortTypeKey = "type";
				break;
			case MOST_RECENT:
				sortTypeKey = "date_create desc";
				break;
			case ITEM_CONDITION:
				sortTypeKey = "item_condition";
				break;
			default:
				sortTypeKey = "title";
				break;
		}

		query = "SELECT COUNT(*) FROM americano.used_item WHERE status REGEXP(?) AND type REGEXP(?) AND title REGEXP(?) AND description REGEXP(?) ORDER BY " + sortTypeKey;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(query);
			preparedStatement.setString(1, applicationStatusKey);
			preparedStatement.setString(2, filterTypeKey);
			preparedStatement.setString(3, delimitedTitleKeywords);
			preparedStatement.setString(4, delimitedDescriptionKeywords);
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getRecordNum()
	{
		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.used_item");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static ArrayList<String> getTypes()
	{
		ArrayList<String> types = new ArrayList<String>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT DISTINCT type FROM americano.used_item");
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				types.add(resultSet.getString(1));
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return types;
	}

	public boolean create()
	{
		int rowAffected = 0;
		boolean isSuccessful = false;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("INSERT INTO americano.used_item (uuid, title, description, type, stock_count, date_create, date_update, item_condition, status, comment, donor_nric) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
			preparedStatement.setString(1, uuid);
			preparedStatement.setString(2, title);
			preparedStatement.setString(3, description);
			preparedStatement.setString(4, type);
			preparedStatement.setInt(5, stockCount);
			preparedStatement.setTimestamp(6, dateCreate);
			preparedStatement.setTimestamp(7, dateUpdate);
			preparedStatement.setString(8, itemCondition.name());
			preparedStatement.setString(9, status.name());
			preparedStatement.setString(10, comment);
			preparedStatement.setString(11, donorNRIC);

			rowAffected = preparedStatement.executeUpdate();
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public boolean delete()
	{
		int rowAffected = 0;
		boolean isSuccessful = false;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("DELETE FROM americano.used_item WHERE id=?");
			preparedStatement.setInt(1, id);

			rowAffected = preparedStatement.executeUpdate();
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public String getComment()
	{
		return comment;
	}

	public Timestamp getDateCreate()
	{
		return dateCreate;
	}

	public Timestamp getDateUpdate()
	{
		return dateUpdate;
	}

	public String getDescription()
	{
		return description;
	}

	public String getDonorNRIC()
	{
		return donorNRIC;
	}

	public int getId()
	{
		return id;
	}

	public ItemCondition getItemCondition()
	{
		return itemCondition;
	}

	public UsedItem getSingle(int id)
	{
		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.used_item WHERE id = ?");
			preparedStatement.setInt(1, id);

			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				this.setAttributes(resultSet);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return this;
	}

	public Status getStatus()
	{
		return status;
	}

	public int getStockCount()
	{
		return stockCount;
	}

	public String getTitle()
	{
		return title;
	}

	public String getType()
	{
		return type;
	}

	public String getUuid()
	{
		return uuid;
	}

	public void setComment(String comment)
	{
		this.comment = comment;
	}

	public void setDateCreate(Timestamp dateCreate)
	{
		this.dateCreate = dateCreate;
	}

	public void setDateUpdate(Timestamp dateUpdate)
	{
		this.dateUpdate = dateUpdate;
	}

	public void setDescription(String description)
	{
		this.description = description;
	}

	public void setDonorNRIC(String donorNRIC)
	{
		this.donorNRIC = donorNRIC;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	public void setItemCondition(ItemCondition itemCondition)
	{
		this.itemCondition = itemCondition;
	}

	public void setStatus(Status status)
	{
		this.status = status;
	}

	public void setStockCount(int stockCount)
	{
		this.stockCount = stockCount;
	}

	public void setTitle(String title)
	{
		this.title = title;
	}

	public void setType(String type)
	{
		this.type = type;
	}

	public void setUuid(String uuid)
	{
		this.uuid = uuid;
	}

	public boolean update()
	{
		int rowAffected = 0;
		boolean isSuccessful = false;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("UPDATE americano.used_item SET uuid=?, title=?, description=?, type=?, stock_count=?, date_update=?, item_condition=?, status=?, comment=?, donor_nric=? WHERE id=?");
			preparedStatement.setString(1, uuid);
			preparedStatement.setString(2, title);
			preparedStatement.setString(3, description);
			preparedStatement.setString(4, type);
			preparedStatement.setInt(5, stockCount);
			preparedStatement.setTimestamp(6, dateUpdate);
			preparedStatement.setString(7, itemCondition.name());
			preparedStatement.setString(8, status.name());
			preparedStatement.setString(9, comment);
			preparedStatement.setString(10, donorNRIC);
			preparedStatement.setInt(11, id);

			rowAffected = preparedStatement.executeUpdate();
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public void setAttributes(ResultSet resultSet)
	{
		try
		{
			this.setComment(resultSet.getString("comment"));
			this.setDateCreate(resultSet.getTimestamp("date_create"));
			this.setDateUpdate(resultSet.getTimestamp("date_update"));
			this.setDescription(resultSet.getString("description"));
			this.setDonorNRIC(resultSet.getString("donor_nric"));
			this.setId(resultSet.getInt("id"));
			this.setItemCondition(ItemCondition.valueOf(resultSet.getString("item_condition")));
			this.setStatus(Status.valueOf(resultSet.getString("status")));
			this.setStockCount(resultSet.getInt("stock_count"));
			this.setTitle(resultSet.getString("title"));
			this.setType(resultSet.getString("type"));
			this.setUuid(resultSet.getString("uuid"));
			this.setWonBy(resultSet.getString("won_by"));

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}

	}

	public boolean updateCloseBid()
	{

		boolean isSuccessful = false;
		int rowAffected = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("UPDATE americano.used_item SET status='ENDED' WHERE id=?");

			preparedStatement.setInt(1, id);

			rowAffected = preparedStatement.executeUpdate();

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public boolean updateWinner(String nric)
	{

		boolean isSuccessful = false;
		int rowAffected = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("UPDATE americano.used_item SET won_by=? WHERE id=?");
			preparedStatement.setString(1, nric);
			preparedStatement.setInt(2, id);

			rowAffected = preparedStatement.executeUpdate();

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public String getWonBy()
	{
		return wonBy;
	}

	public void setWonBy(String wonBy)
	{
		this.wonBy = wonBy;
	}

	public enum ItemCondition
	{
		USED, NEW, PARTS_OR_NOT_WORKING, OTHER;
	}

	public enum SortBy
	{
		TITLE, TYPE, MOST_RECENT, ITEM_CONDITION;
	}

	public enum Status
	{
		APPROVED, PENDING, REJECTED, ENDED;
	}

	public enum Type
	{
		FURNITURE, APPLIANCE, HOUSEHOLD_ITEMS, DRY_FOOD, CLOTHES, ACCESSORIES, BOOKS;
	}
}
